package com.luoling.readExcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.luoling.util.JsonUtils;
import lombok.Data;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.session.SqlSessionManager;

import java.io.File;
import java.io.FileReader;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * Description: 读取excel里的sql语句并且在对应数据库执行 保存执行后的结果集
 * Copyright:   Copyright (c)2024
 * Company:
 *
 * @author: 张李鑫
 * @version: 1.0
 * Create at:   2024-07-09 17:40:40
 * <p>
 * Modification History:
 * Date         Author      Version     Description
 * ------------------------------------------------------------------
 * 2024-07-09     张李鑫                     1.0         1.0 Version
 */
public class ReadExcelV2 {

    public static ExecutorService executor = Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());


    private static CountDownLatch countDownLatch;

    /**
     * {
     * "fileName": "src/main/java/com/luoling/1.xlsx",
     * "sheet": "0,1",
     * "sql": "10,10",
     * "needFilter": true,
     * "rule": {
     * "index": 1,
     * "comparison": "EQUALS",
     * "value": "是"
     * }
     * }
     *
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        System.out.println("线程池大小:" + Runtime.getRuntime().availableProcessors() + "开始时间" + DateUtils.format(new Date()));
        long l = System.currentTimeMillis();
        ReadExcelForm readExcelForm = readExcelConfig(args);
        Map<Integer, List<ExcelModel>> writeTodo = buildWriteList(readExcelForm);//构造输出excel
        AtomicInteger size = new AtomicInteger();
        writeTodo.forEach((k, v) -> size.addAndGet(v.size()));
        countDownLatch = new CountDownLatch(size.get());
        SqlSessionFactory sqlSessionFactory = DbUtils.readMybatisConfig(readExcelForm.getMybatisConfig());
        try (SqlSessionManager sqlSessionManager = SqlSessionManager.newInstance(sqlSessionFactory)) {
            SqlSession sqlSession = sqlSessionManager.openSession();
            executeSql(writeTodo, sqlSession);
            countDownLatch.await();
            executor.shutdownNow();
            doWrite(readExcelForm, writeTodo);
            System.out.println("耗时:" + (System.currentTimeMillis() - l) + "ms" + "结束时间" + DateUtils.format(new Date()));
        }
    }


    public static void executeSql(Map<Integer, List<ExcelModel>> writeTodo, SqlSession sqlSession) throws Exception {
        writeTodo.forEach((k, v) -> {
            try {
                execute(v, sqlSession);
            } catch (Exception e) {
                System.out.println(e.getMessage());
                throw new RuntimeException(e);
            }
        });
    }


    public static void doWrite(ReadExcelForm readExcelForm, Map<Integer, List<ExcelModel>> writeTodo) {
        ExcelWriterBuilder write = EasyExcel.write(new Date().getTime() + readExcelForm.getFileName());
        try (ExcelWriter excelWriter = write.build()) {
            writeTodo.forEach((k, v) -> {
                WriteSheet sheet1 = EasyExcel.writerSheet(k, k.toString()).build();
                excelWriter.write(v, sheet1);
            });
        }
    }


    public static Map<Integer, List<ExcelModel>> buildWriteList(ReadExcelForm readExcelForm) {
        String[] sqlArrays = readExcelForm.getSql().split(",");
        String[] sheetArray = readExcelForm.getSheet().split(",");

        Map<Integer, List<ExcelModel>> writeTodo = new HashMap<>();

        for (int i = 0; i < sheetArray.length; i++) {
            int sheet = Integer.parseInt(sheetArray[i]);
            Rule rule = readExcelForm.getRules().get(i);
            List<Map<Integer, String>> excelList = readExcel(readExcelForm, sheet, rule);
            ArrayList<ExcelModel> excelModels = buildExcelModels(excelList, rule, Integer.parseInt(sqlArrays[i]));
            writeTodo.put(sheet, excelModels);
        }
        return writeTodo;
    }


    public static ArrayList<ExcelModel> buildExcelModels(List<Map<Integer, String>> excelList, Rule rule, Integer sqlIndex) {
        ArrayList<ExcelModel> excelModels = new ArrayList<>();
        for (Map<Integer, String> map : excelList) {
            String id = map.get(rule.getIndex());
            String sql = map.get(sqlIndex);
            excelModels.add(new ExcelModel(id, sql));
        }
        return excelModels;
    }

    public static ReadExcelForm readExcelConfig(String[] args) throws Exception {
        if (args.length == 0) {
            throw new RuntimeException("没有参数");
        }
        if (args.length < 2 || !args[0].equals("--config")) {
            System.err.println("Usage: java -jar MyApp.jar --config /path/to/config.properties");
            System.exit(1);
        }
        String configPath = args[1];
        if (configPath.contains("./")) {
            configPath = System.getProperty("user.dir") + File.separator + configPath.replace("./", "");
        }
        FileReader reader = new FileReader(configPath);
        JsonNode jsonNode = JsonUtils.MAPPER.readTree(reader);
        return JsonUtils.MAPPER.treeToValue(jsonNode, ReadExcelForm.class);

    }


    public static List<Map<Integer, String>> readExcel(ReadExcelForm readExcelForm, Integer sheet, Rule rule) {
        String fileName = System.getProperty("user.dir") + File.separator + readExcelForm.getFileName();
        Boolean needFilter = readExcelForm.getNeedFilter();
        // 读取 Excel 文件中的数据
        List<Map<Integer, String>> dataList = new ArrayList<>();

        ExcelReaderBuilder readerBuilder = EasyExcel.read(fileName);
        readerBuilder.sheet(sheet).registerReadListener(new ReadListener<Map<Integer, String>>() {
            @Override
            public void invoke(Map<Integer, String> data, AnalysisContext context) {
                if (!needFilter) {
                    dataList.add(data);
                    return;
                }
                //需要过滤
                if (rule != null && rule.filter(data)) {
                    dataList.add(data);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 全部读取完成时调用
                System.out.println("excel sheet " + sheet + " 读取完毕");
            }

            @Override
            public void onException(Exception exception, AnalysisContext context) {
                // 处理读取过程中的异常
//                exception.printStackTrace();
            }
        }).doRead();
        return dataList;
    }


    public static void execute(List<ExcelModel> models, SqlSession sqlSession) throws Exception {
        for (ExcelModel model : models) {
            executor.submit(() -> {
                try {
                    Connection connection = sqlSession.getConnection();
                    if (model.getSql().contains("select") || model.getSql().contains("SELECT")) {
                        PreparedStatement preparedStatement = connection.prepareStatement(model.getSql());
                        preparedStatement.setQueryTimeout(300);
                        ResultSet resultSet = preparedStatement.executeQuery();
                        String json = DbUtils.toJson(resultSet);
                        model.setJson(json);
                    } else {
                        model.setMessage("非select语句");
                    }
                    model.setResult(model.getJson().equals("[]")?"通过":"不通过");
                } catch (Exception exception) {
                    model.setMessage(exception.getMessage());
                } finally {
                    countDownLatch.countDown();
                }
            });
        }
    }

}

